import datetime
import os.path
import random
import re

import numpy as np
import pandas as pd
import tortoise.transactions

from instructions.instruction import Instruction
import logging

from instructions.enum_types import InstructionType, ActionType, ExcelType
from instructions.static_instruction import StaticInstruction
from models import Customers, Order, Kumquats
from tools import compose_attachment_name, over_deadline, action_logger, beatify_df_row
from wechat.reply import Reply, ReplyType
from config import config, kqConfig

logger = logging.getLogger(__name__)


class ImportInstruction(Instruction):
    """
    This class of Instruction is used to handle the file loading. No AI used.
    The format is checked, so no worry about it.
    In this case, query argument is supposed to be the reference information and the user's instruction words.
    """

    def __init__(self, chatDBHandler=None, query=None, force_flag=False):
        super().__init__(chatDBHandler, query)
        self.insType = InstructionType.IMPORT_EXCEL_ORDER
        self.chatDBHandler = chatDBHandler
        self.prompt = """"""
        self.query = query
        self.orderPre = None
        self.kumquatPre = None
        self.force_flag = force_flag
        logger.info(f"[instruction] Init Instruction {self.insType}")
        self.help = "请引用你要导入的文件，并@我。 可以说导入，导文件，导金桔，导订单都行。\n" \
                    "如果需要使用默认的金桔模板，可以说直接导入。 注意，金桔的数据属于你的小秘密，不要在群里发给我噢，请私聊给我~[坏笑]"

    async def handle_query(self) -> Reply:
        """
        还有这个，导入 「慌雨：[文件]顺丰打单241210.xls」 - - - - - - - - - - - - - - -
        :return:
        """
        fname = self.get_fname_from_query()
        if fname and os.path.exists(fname):
            excelCH = pd.read_excel(fname)
            try:
                self.chatDBHandler.add_actions(ActionType.CHECK_MSG_TYPE)
                if ExcelType.ORDER == StaticInstruction.check_file_type(excelCH.columns):  # 检测导入类型
                    logger.info(f"[instruction] Order file detected.")
                    self.chatDBHandler.add_actions(ActionType.CHECK_DEADLINE)
                    delta = over_deadline()
                    if delta.days < 0 and config.PACKAGE_DEADLINE_ON:
                        self.chatDBHandler.add_actions(ActionType.OVER_DEADLINE)
                        logger.warning("[deadline check] Order loaded is over time. Skip!")
                        return Reply(ReplyType.TEXT, StaticInstruction.generate_over_deadline_msg(delta, True))

                    reverseHeader = {v: k for k, v in config.ORDER_HEADERS.items()}
                    self.orderPre = excelCH.rename(columns=reverseHeader)

                elif ExcelType.KUMQUAT == StaticInstruction.check_file_type(excelCH.columns):
                    if not self.chatDBHandler.msg.is_group:
                        logger.info(f"[instruction] Kumquat file detected.")
                        reverseHeader = {v: k for k, v in config.KUMQUATS_HEADERS.items()}
                        self.kumquatPre = excelCH.rename(columns=reverseHeader)
                    else:
                        return Reply(ReplyType.TEXT, "金桔表格属于敏感信息，请私下发给我噢~[嘿哈]，我嘴很严！")
                else:
                    return Reply(ReplyType.TEXT, self.help)
                res = await self.clean_excel_data()
                if res != 'OK':
                    os.remove(fname)
                    self.chatDBHandler.add_actions(ActionType.DELETE_BAD_FILE)
                    logger.info(f"[instruction] 数据清洗失败，移除错误文件{fname}。")
                    return Reply(ReplyType.TEXT, res)

                res = await self.add_to_db()
                if res != 'OK':
                    return Reply(ReplyType.TEXT, res)
                res = await self.report_order_info(fname)
                # Rename file to done
                os.rename(fname, fname + '.done')
                self.chatDBHandler.add_actions(ActionType.RENAME_TO_DONE)
                self.chatDBHandler.note = fname + '.done'
                logger.info("[file] Cleaned, Inserted, Reported and Renamed file.")
                return Reply(ReplyType.TEXT, res)
            except Exception as e:
                logger.error(f"[instruction] {e}")
                logger.exception(e)
                self.chatDBHandler.add_actions(ActionType.IMPORT_FAILED)
                return Reply(ReplyType.TEXT, "文件格式有点问题吧...叫我爹看看[可怜]？")
        else:
            return Reply(ReplyType.TEXT,
                         "这个文件我找不到了，一定要是今天传给我的文件，昨天的我看不到了。你要不再传一次？[可怜]")

    def get_fname_from_query(self):
        match = re.search(r'「(.*?)」', self.query)
        if match:
            fnames = match.group(1).split('：')[-1].replace('[文件]', '')
            return compose_attachment_name(self.chatDBHandler.msg.is_group, self.chatDBHandler.msg.actual_user_nickname,
                                           self.chatDBHandler.msg.from_user_nickname, fnames)

    @action_logger(ActionType.INSERT_EXCEL)
    async def add_to_db(self):
        try:
            if self.orderPre is not None and not self.orderPre.empty:
                customersToInsert = []
                orders = []
                for _, row in self.orderPre.iterrows():

                    customer = Customers(name=row['customers__name'], phone=row['customers__phone'],
                                         address=row['customers__address'],
                                         presenter=row['customers__presenter'])
                    isOldCust = await customer.filter().first()  # ⚠️ 如果找不到返回NoneType
                    if not isOldCust:
                        for ele in customersToInsert:
                            if customer == ele:
                                continue
                        customersToInsert.append(customer)

                logger.info(f"[process db] Bulk insert non exists customer to DB. "
                            f"New Customer size: {len(customersToInsert)}")
                async with tortoise.transactions.in_transaction():
                    await Customers.bulk_create(customersToInsert)

                amounts = []  # Need to Add amount to order dataframe
                for _, row in self.orderPre.iterrows():
                    if row['quantity'] == 0:
                        logger.warning("[process db] 发现一单数量为0的，为了避免之后打单出问题，直接忽略。")
                        continue
                    # ⚠️ 这里好像可以把金桔的ID在数据清洗阶段就加入了，不用再做查询
                    # 不行，这样order.get_amount()会报错
                    kumquat = await self.chatDBHandler.check_user_has_kumquat(row['kumquats__name'],
                                                                              row['kumquats__format'], self.force_flag)
                    customer = await Customers.filter(name=row['customers__name'],
                                                      phone=row['customers__phone'],
                                                      address=row['customers__address'],
                                                      presenter=row['customers__presenter']).first()
                    order = Order(kumquats=kumquat, customers=customer, users_id=self.chatDBHandler.user_id,
                                  quantity=row['quantity'], on_sold=row['on_sold'], status=row['status'],
                                  note=row['note'], book_date=row['book_date'])
                    order.get_amount()
                    amounts.append(order.amount)
                    orders.append(order)
                async with tortoise.transactions.in_transaction():
                    await Order.bulk_create(orders)

                self.orderPre['amount'] = amounts
                logger.info(f"[process db] Bulk insert order to DB. New Order size: {len(orders)} "
                            f"for {self.chatDBHandler.nn}")
            else:  # Kumquat data insert
                for _, row in self.kumquatPre.iterrows():
                    k = Kumquats(name=row['name'], format=row['format'], packing=row['packing'],
                                 original_price=row['original_price'], price=row['price'],
                                 pricing_date=row['pricing_date'],
                                 note=row['note'], users_id=self.chatDBHandler.user_id)
                    # 更改旧数据note
                    await Kumquats.filter(name=row['name'], format=row['format'], note='CURRENT',
                                          users_id=self.chatDBHandler.user_id).update(note='OLD')
                    # logger.info(f"[process db] Update {row['name']} {row['format']} kumquats to OLD.")
                    await k.save()
                logger.info(f"[process db] Bulk insert order to DB. New Order size: {self.kumquatPre.shape[0]} "
                            f"for {self.chatDBHandler.nn}")

            return 'OK'
        except Exception as e:
            self.chatDBHandler.add_actions(ActionType.INSERT_EXCEL_FAILED)
            self.chatDBHandler.note = str(e)
            logger.error(f"[process db] Trying to insert data to database, but failed..{e}")
            logger.exception(e)
            return f'在插入数据库阶段出错了, 这是错误- {e}..\n要不叫我爹来看看？[委屈]'

    @action_logger(ActionType.REPORT_LOADED_FILE)
    async def report_order_info(self, fname) -> str:
        if self.orderPre is not None and not self.orderPre.empty:
            df_report = self.classify_kumquats_data(self.orderPre)
            reportStr = f"这是本次收录的订单【{os.path.basename(fname)}】报告，请检查：\n 🌟 一共有【{self.orderPre.shape[0]}】 项订单，其中：\n"
            headers = df_report.columns
            for _, row in df_report.iterrows():  # TODO: 把硬编码改了
                given_info = f"送人【{row['赠送金桔']}】箱，" if row['赠送金桔'] else ''
                reportStr += f"🍊 【{row['金桔名称']}】有【{row['数量']}箱】，" \
                             f"{given_info}【交易金额】：【{row['交易金额']}】元。\n"
            amount = df_report['交易金额'].sum()
            reportStr += f"本次总交易金额为【{amount}】元。\n"
            reportStr += self.build_report_end(amount)
        else:
            available_kumquats = await self.chatDBHandler.get_users_kumquats(force_flag=True)
            reportStr = "收录完毕[呲牙]！这是你目前的金桔规格和价格：\n" + self.build_kumquat_str(available_kumquats,
                                                                                                details=True)
        return reportStr

    def build_report_end(self, amount):
        end = "汇报完了，累死了..想要[红包][可怜]\n"
        if amount > kqConfig.SUPRISE_AMOUNT and self.chatDBHandler.nn != config.MY_DAD_NN:
            flag = True
            while flag:
                end = random.choice(kqConfig.SUPRISE_AMOUNT_REPLY)
                if config.MY_DAD_NN not in end:
                    flag = False
        elif amount > kqConfig.SUPRISE_AMOUNT and self.chatDBHandler.nn == config.MY_DAD_NN:
            end = random.choice(kqConfig.SUPRISE_AMOUNT_REPLY + kqConfig.SUPRISE_AMOUNT_DAD_REPLY)
        elif amount < kqConfig.LOSER_AMOUNT and self.chatDBHandler.nn != config.MY_DAD_NN:
            flag = True
            while flag:
                end = random.choice(kqConfig.LOSER_AMOUNT_REPLY)
                if config.MY_DAD_NN not in end:
                    flag = False
        elif amount < kqConfig.LOSER_AMOUNT and not self.chatDBHandler.nn == config.MY_DAD_NN:
            end = random.choice(kqConfig.LOSER_AMOUNT_REPLY + kqConfig.LOSER_AMOUNT_DAD_REPLY)
        return end

    @action_logger(ActionType.CLEAN_EXCEL_DATA)
    async def clean_excel_data(self) -> str:
        """
        1. 转换金桔为统一格式，把非必须的空值补充完整
        2. 检测金桔规格是否正确，并做转化，是否都存在于用户的金桔库里，如果 force_flag = True 则还会匹配默认的金桔
        3. 检测数据中的必要项是否存在空值
        :return: 清洗成功返回 OK， 清洗失败返回具体原因作为回复
        """
        res = ""
        if self.orderPre is not None and not self.orderPre.empty:
            self.orderPre['kumquats__name'] = self.convert_kumquat_str(self.orderPre['kumquats__name'])
            self.orderPre['kumquats__format'] = self.convert_kumquat_str(self.orderPre['kumquats__format'])
            logger.info("[pre import] Standardize the kumquat format.")

            self.orderPre.fillna({'on_sold': 1}, inplace=True)
            self.orderPre.fillna({'customers__presenter': '我'}, inplace=True)
            self.orderPre.fillna({'book_date': datetime.datetime.now()}, inplace=True)
            # self.orderPre.fillna({'status': '已下单'}, inplace=True)
            self.orderPre['status'] = '已下单'
            logger.debug(f"[pre import] Fill null value for orders {self.orderPre} for {self.chatDBHandler.nn}")

            for _, row in self.orderPre.iterrows():
                k = await self.chatDBHandler.check_user_has_kumquat(
                    row['kumquats__name'], row['kumquats__format'], self.force_flag)
                if k:
                    if not row[kqConfig.ORDER_FILE_REQUIRED_HEADERS].isna().any():
                        try:
                            float(row['on_sold'])
                            int(row['quantity'])
                        except ValueError:
                            self.chatDBHandler.add_actions(ActionType.ORDER_VALUE_ERROR)
                            logger.error(f"[pre import] Order value error: {row} for {self.chatDBHandler.nn}")
                            res += f"👾 第{_}行：{config.KUMQUATS_HEADERS[row]}的折扣或数量不是数字呀! \n"
                        continue
                    else:
                        required = [config.ORDER_HEADERS[rcol] for rcol in kqConfig.ORDER_FILE_REQUIRED_HEADERS]
                        self.chatDBHandler.add_actions(ActionType.ORDER_FIEDLS_EMPTY)
                        logger.error(
                            f"[pre import] Order required fields error: {row} for {self.chatDBHandler.nn}")
                        res += f"👾 第{_}行：{beatify_df_row(row)}的必填项：{required} 有空值!\n"
                else:
                    available_kumquats = await self.chatDBHandler.get_users_kumquats(self.force_flag)
                    available_kumquats_str = self.build_kumquat_str(available_kumquats)
                    self.chatDBHandler.add_actions(ActionType.ORDER_KUMQUAT_FIELDS_ERROR)
                    logger.error(
                        f"[pre import] Order kumquat fields non exists: {row} for {self.chatDBHandler.nn}")
                    res += f"👾 第{_}行：{beatify_df_row(row)}的金桔规格不对, 这是你现在可用的金桔规格：\n {available_kumquats_str} " \
                           f"\n 🌟 请确保你的订单金桔规格在其中，或者可以导入这个规格的金桔后再导入这份订单。"
                    return res  # ⚠️ 金桔规格不对是比较严重的错误，一旦发现直接返回
            self.orderPre['customers__phone'].astype(str)
        else:
            self.kumquatPre['name'] = self.convert_kumquat_str(self.kumquatPre['name'])
            self.kumquatPre['format'] = self.convert_kumquat_str(self.kumquatPre['format'])
            logger.info("[pre import] Standardize the kumquat format.")

            self.kumquatPre.fillna({'pricing_date': 0}, inplace=True)
            self.kumquatPre['pricing_date'] = datetime.datetime.now().date()
            self.kumquatPre['packing'] = '0'
            self.kumquatPre['note'] = 'CURRENT'
            logger.debug(f"[pre import] Fill null value for kumquats {self.kumquatPre}")

            for col in kqConfig.KUMQUAT_FILE_REQUIRED_HEADERS:
                if self.kumquatPre[col].isna().any():
                    self.chatDBHandler.add_actions(ActionType.KUMQUAT_FIEDLS_EMPTY)
                    logger.info(f"[pre import] Required column {col} has null value for {self.chatDBHandler.nn}.")
                    res += f"👾【{config.KUMQUATS_HEADERS[col]}】列的数据有空值，必填项为：" \
                           f" {[config.KUMQUATS_HEADERS[rcol] for rcol in kqConfig.KUMQUAT_FILE_REQUIRED_HEADERS]} \n"
                if (col == 'price' or col == 'original_price') and not self.is_column_numeric(self.kumquatPre[col]):
                    logger.info(
                        f"[pre import] Required column {col} has non-numeric value for {self.chatDBHandler.nn}.")
                    self.chatDBHandler.add_actions(ActionType.KUMQUAT_VALUE_ERROR)
                    res += f"👾【{config.KUMQUATS_HEADERS[col]}】列的数据必须是数字! 认真点~[白眼]\n"
        return 'OK' if len(res) == 0 else res

    @staticmethod
    def build_kumquat_str(available_kumquats, details=False) -> str:
        res = ""
        if details:
            for k in available_kumquats:
                # default_price = ' - 缺省' if k.users_id == 0 else ''
                if k.users_id == 0:
                    continue
                res += f"🍊 {k.name} {k.format} -" \
                       f"【原价】{float(k.original_price)}元, 【售卖】{float(k.price)}元\n"
        else:
            for k in available_kumquats:
                if k.users_id == 0:
                    continue
                # default_price = ' - 缺省' if k.users_id == 0 else ''
                res += f"🍊 {k.name} {k.format}\n"
        return res

    @staticmethod
    def is_column_numeric(col: pd.Series) -> bool:
        # 尝试将列转换为数字，转换失败的值会被设置为 NaN
        numeric_col = pd.to_numeric(col, errors='coerce')
        if numeric_col.isna().any():
            return False
        else:
            return True

    @staticmethod
    def convert_kumquat_str(col: pd.Series):
        """
        将金桔规格转换为标准格式
        :param col:
        :return:
        """
        return col.replace(kqConfig.FORMAT_CH_MAPPING)

    @staticmethod
    def classify_kumquats_data(df: pd.DataFrame) -> pd.DataFrame:
        """
        把订单变成报告格式：【金桔】【数量】【金额】【赠送】【不要礼盒】
        :param df: 订单dataframe
        :return: 关于输入订单的报告
        """

        def contains_any(text, keywords):
            if str(text) == 'nan' or text is None or text == np.nan:
                return False
            return any(keyword in text for keyword in keywords)

        df['kumquats_style'] = df['kumquats__name'] + ' ' + df['kumquats__format']
        df2Report = pd.DataFrame(columns=config.REPORT_HEADERS.keys())
        df2Report['kumquats_style'] = df['kumquats_style'].unique()
        quantities = []
        amounts = []
        givens = []
        no_beautiful_package = []
        for style in df2Report['kumquats_style'].tolist():
            quantities.append(df[df['kumquats_style'] == style]['quantity'].sum())
            amounts.append(df[df['kumquats_style'] == style]['amount'].sum())
            givens.append(df[(df['kumquats_style'] == style) &
                             df['note'].str.contains(kqConfig.GIVEN_KEYS, na=False)]['quantity'].sum())
            no_beautiful_package.append(df[(df['kumquats_style'] == style) &
                                           df['note']
                                        .apply(lambda x: contains_any(x, kqConfig.NO_BEAUTIFUL_PACKAGE_KEYS))]
                                        ['quantity'].sum())  # 真够特么绕的
        df2Report['quantity'] = quantities
        df2Report['amount'] = amounts
        df2Report['given'] = givens
        df2Report['no_beautiful_package'] = no_beautiful_package

        # reverseHeader = {v: k for k, v in config.REPORT_HEADERS.items()}
        df2Report.rename(columns=config.REPORT_HEADERS, inplace=True)
        logger.debug(f"[report] Classify kumquats data {df2Report}")
        return df2Report
